package iedcyt.acute.dataAccess.dao.maauco;

import iedcyt.acute.dataAccess.domain.acute1.Author;
import iedcyt.acute.dataAccess.domain.acute1.Center;
import iedcyt.acute.dataAccess.domain.maauco.Au0;
import iedcyt.acute.dataAccess.domain.maauco.Co0;
import iedcyt.acute.dataAccess.generic.SpringDao;
import iedcyt.acute.dataAccess.idao.maauco.ICo0Dao;

import java.util.List;



public class Co0Dao extends SpringDao implements ICo0Dao  {

	/**
	 * @see iedcyt.acute.dataAccess.idao.maauco.IAu0Dao#create()
	 */
	@Override
	public void create() {
		//comprobamos si la tabla existe y si no la creamos
		try {
			_jdbcTemplate.queryForInt("select count(*) from Co0");
		} catch (Exception e) {
			String sqlins = "CREATE TABLE Co0 (IDNO numeric, NO numeric, NUMADD numeric, CORP varchar(255), COUNTRY varchar(100), YEAR varchar(4), PROVINCIA varchar(2), INSTIT varchar(4), ORGANISMO varchar(6), DPTO varchar(8), ISO varchar(2), AUTH varchar(100))";
			String sqlidx = "CREATE INDEX idxCo0 on Co0 (idno, no) WITH PRIMARY DISALLOW NULL";
			_log.info(sqlins);
			_log.info(sqlidx);
			_jdbcTemplate.execute(sqlins);
			_jdbcTemplate.execute(sqlidx);
			
		}
		
		//Creamos indices
		try {
			String sqlidxCo0 = "CREATE INDEX idxCo0PIOD ON Co0 (PROVINCIA,INSTIT,ORGANISMO ,DPTO)";
			_log.info(sqlidxCo0);
			_jdbcTemplate.execute(sqlidxCo0);
		} catch (Exception e) {
			_log.error("Error creando indices en Co0",e);
		}
	}
	
	@Override
	public List<Co0> selectAll() {
		
		return _jdbcTemplate.query("SELECT * FROM Co0",
				new Co0RowMapper());
	}
	
	@Override
	public void deleteAll() {
		
		_jdbcTemplate.update("DELETE from Co0");
	}

	

	@Override
	public void insert(Co0 co0) {
	
		_jdbcTemplate.update("INSERT INTO Co0 (IDNO, NO, NUMADD, CORP, COUNTRY, YEAR, PROVINCIA, INSTIT, ORGANISMO, DPTO, ISO, AUTH) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
				new Object[] { co0.get_IDNO(), co0.get_NO(), co0.get_NUMADD(), co0.get_CORP(),co0.get_COUNTRY(), co0.get_YEAR(), co0.get_PROVINCIA(), co0.get_INSTIT(),
				co0.get_ORGANISMO(), co0.get_DPTO(), co0.get_ISO(), co0.get_AUTH() });
		
	}
	
	@Override
	public void update(Co0 doc) {
	
		_jdbcTemplate.update("UPDATE Co0 SET  NUMADD=?, CORP=?, COUNTRY= ?, YEAR=?, PROVINCIA= ?, INSTIT= ?, ORGANISMO= ?, DPTO =? , ISO= ?, AUTH = ? WHERE IDNO = ? AND NO = ?",
				new Object[] { doc.get_NUMADD(), doc.get_CORP(), doc.get_COUNTRY(), doc.get_YEAR(),doc.get_PROVINCIA(), doc.get_INSTIT(),
				doc.get_ORGANISMO(), doc.get_DPTO(), doc.get_ISO(), doc.get_AUTH(), doc.get_IDNO(), doc.get_NO() });
		
	}

	@Override
	public void delete(int idno, int no) {
	
		_jdbcTemplate.update("DELETE from Co0 WHERE idno=? AND no=?",
				new Object[] { idno, no  });
	}
	
	@Override
	public List<Co0> selectByPIO(String prov, String instit, String org) {
		return _jdbcTemplate
		.query(
				"SELECT * FROM Co0 WHERE PROVINCIA LIKE ? AND INSTIT LIKE ? AND ORGANISMO LIKE ?",
				new Object[] { prov, instit, org },
				new Co0RowMapper());
	}
	
	@Override
	public List<Co0> selectByPIOByRange(String prov, String instit, String org, int idno1, int idno2) {
		return _jdbcTemplate
		.query(
				"SELECT * FROM Co0 WHERE PROVINCIA LIKE ? AND INSTIT LIKE ? AND ORGANISMO LIKE ? AND IDNO >= ? AND IDNO <= ?",
				new Object[] { prov, instit, org, idno1, idno2 },
				new Co0RowMapper());
	}

	@Override
	public List<Co0> selectByIdno(int idno) {
		return _jdbcTemplate
		.query(
				"SELECT * FROM Co0 WHERE IDNO = ?",
				new Object[] { idno },
				new Co0RowMapper());
	}

	@Override
	public List<Co0> selectByIdnoRange(int idno1, int idno2) {
		return _jdbcTemplate
		.query(
				"SELECT * FROM Co0 WHERE IDNO >= ? AND IDNO <= ?",
				new Object[] { idno1, idno2 },
				new Co0RowMapper());
	}

	@Override
	public List<Co0> select(int idno, int no) {
		return _jdbcTemplate
		.query(
				"SELECT * FROM Co0 WHERE IDNO = ? AND NO = ?",
				new Object[] { idno, no },
				new Co0RowMapper());
	}

	@Override
	public int selectMaxIdno() {
		return _jdbcTemplate.queryForInt("SELECT MAX(IDNO) FROM Co0");
	}

	@Override
	public List<Co0> selectByPIOByIdno(int idno, String prov, String instit,
			String org) {
		return _jdbcTemplate
		.query(
				"SELECT * FROM Co0 WHERE IDNO = ? AND PROVINCIA = ? AND INSTIT = ? AND ORGANISMO = ?",
				new Object[] { idno, prov, instit, org },
				new Co0RowMapper());
	}
	
	@Override
	public List<Co0> selectByPIOsByIdno(List<Au0> aus, List<Center> centers) {
		
		int cnt = 0;
		
		String where = "(";
		for (int j = 0; j < aus.size(); j++) {
			where += " IDNO = ? OR";
			cnt++;
		}
		
		where += ") AND (";
		
		where = where.replace("OR) AND (", ") AND (");
		
		for (int i = 0; i < centers.size(); i++) {
			
			where += " (PROVINCIA = ? AND INSTIT = ? AND ORGANISMO = ?) OR";
			cnt = cnt + 3;
		}
		
		where += ")";
		
		where = where.replace("OR)", ")");
		
		Object[] objs = new Object[cnt];
		
		int aux = 0;
		for (Au0 au: aus) {
			objs[aux] = au.get_idno();
			aux++;
		}
			
		for (Center cen: centers) {
			objs[aux] = cen.get_PROVINCIA();
			aux++; objs[aux] = cen.get_INSTIT();
			aux++; objs[aux] = cen.get_ORGANISMO();
			aux++;
		}
		                           
		return _jdbcTemplate
		.query(
				"SELECT * FROM Co0 WHERE " + where,
				objs,
				new Co0RowMapper());
	}


	@Override
	public List<Co0> selectByIdnoByCorp(int idno, String corp){
		return _jdbcTemplate
		.query(
				"SELECT * FROM Co0 WHERE IDNO = ? AND CORP = ? ",
				new Object[] { idno, corp },
				new Co0RowMapper());
	}
	
}

